# language: zh-CN
# Epic 3.2: 高级查询功能
# 需求ID: REQ-003.2
# SRS需求: 系统应支持高级查询功能，包括子查询、窗口函数、CTE(公用表表达式)、动态查询构建、复杂聚合查询、全文搜索、地理空间查询、时间序列查询等企业级查询能力
# Feature ID: FT-003.2
# 关联Epic: EP-003 (数据访问层)
# 优先级: 高
# 复杂度: 高

# 用户故事:
# US-070: 作为开发者，我希望支持子查询(包括相关子查询、EXISTS查询)，以便实现复杂的数据查询逻辑
# US-071: 作为开发者，我希望支持窗口函数(ROW_NUMBER、RANK、SUM等)，以便进行高级数据分析和统计
# US-072: 作为开发者，我希望支持CTE和递归查询，以便简化复杂查询的编写和维护
# US-073: 作为开发者，我希望支持动态查询构建器，以便根据条件灵活构建查询语句
# US-074: 作为开发者，我希望支持复杂聚合查询和分组统计，以便进行数据统计分析
# US-075: 作为开发者，我希望支持全文搜索和模糊匹配，以便实现智能搜索功能
# US-076: 作为开发者，我希望支持地理空间查询和GIS功能，以便实现基于位置的应用功能
# US-077: 作为开发者，我希望支持时间序列查询和时间窗口分析，以便分析时间相关的业务数据

# 验收标准:
# - 支持各种类型的子查询，包括IN、EXISTS、相关子查询等
# - 支持完整的窗口函数集合，包括排名、聚合、偏移函数
# - 支持CTE和递归CTE，简化复杂查询编写
# - 提供灵活的动态查询构建API，支持条件组合
# - 支持复杂的聚合和分组查询，包括HAVING子句
# - 集成全文搜索引擎，支持中文分词和相关性排序
# - 支持地理空间数据类型和空间查询函数
# - 提供时间序列查询优化和时间窗口分析功能

功能: 高级查询功能
  作为开发者
  我希望系统提供高级查询功能
  以便实现复杂的数据查询需求

  背景:
    假设我是一个开发者
    并且我需要执行复杂的数据查询
    并且我希望查询功能强大且易用
    并且我需要优化查询性能

  # 用户故事: US-070 - 支持子查询实现复杂的数据查询逻辑
  场景: 子查询支持
    假设我需要使用子查询进行复杂查询
    当我构建包含子查询的查询时:
      """
      const users = await userRepo.find({
        where: {
          id: {
            $in: {
              $subquery: {
                select: 'user_id',
                from: 'orders',
                where: {
                  status: 'completed',
                  created_at: { $gte: '2024-01-01' }
                }
              }
            }
          }
        }
      });
      """
    那么应该生成正确的SQL子查询:
      """
      SELECT u.*
      FROM users u
      WHERE u.id IN (
        SELECT o.user_id
        FROM orders o
        WHERE o.status = 'completed'
          AND o.created_at >= '2024-01-01'
      );
      """
    并且应该优化子查询性能
    并且应该支持嵌套子查询
    并且应该支持相关子查询
    并且应该支持EXISTS和NOT EXISTS子查询

  # 用户故事: US-071 - 支持窗口函数进行高级数据分析
  场景: 窗口函数支持
    假设我需要使用窗口函数进行分析查询
    当我使用窗口函数时:
      """
      const result = await orderRepo.findWithWindow({
        select: [
          'id',
          'user_id',
          'amount',
          {
            $window: {
              function: 'ROW_NUMBER',
              over: {
                partitionBy: 'user_id',
                orderBy: { created_at: 'DESC' }
              },
              alias: 'order_rank'
            }
          },
          {
            $window: {
              function: 'SUM',
              column: 'amount',
              over: {
                partitionBy: 'user_id',
                orderBy: { created_at: 'ASC' },
                frame: 'ROWS UNBOUNDED PRECEDING'
              },
              alias: 'running_total'
            }
          }
        ],
        where: { status: 'completed' }
      });
      """
    那么应该生成窗口函数SQL:
      """
      SELECT 
        id,
        user_id,
        amount,
        ROW_NUMBER() OVER (
          PARTITION BY user_id 
          ORDER BY created_at DESC
        ) AS order_rank,
        SUM(amount) OVER (
          PARTITION BY user_id 
          ORDER BY created_at ASC 
          ROWS UNBOUNDED PRECEDING
        ) AS running_total
      FROM orders
      WHERE status = 'completed';
      """
    并且应该支持常用窗口函数（ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD等）
    并且应该支持自定义窗口框架
    并且应该优化窗口函数性能

  # 用户故事: US-072 - 支持CTE简化复杂查询的编写
  场景: 公用表表达式(CTE)支持
    假设我需要使用CTE简化复杂查询
    当我使用CTE查询时:
      """
      const result = await userRepo.findWithCTE({
        with: {
          active_users: {
            select: ['id', 'name', 'email'],
            from: 'users',
            where: { status: 'active', last_login: { $gte: '2024-01-01' } }
          },
          user_stats: {
            select: [
              'user_id',
              { $count: '*', alias: 'order_count' },
              { $sum: 'amount', alias: 'total_amount' }
            ],
            from: 'orders',
            where: { status: 'completed' },
            groupBy: 'user_id'
          }
        },
        select: [
          'au.id',
          'au.name',
          'au.email',
          'COALESCE(us.order_count, 0) as order_count',
          'COALESCE(us.total_amount, 0) as total_amount'
        ],
        from: 'active_users au',
        leftJoin: {
          table: 'user_stats us',
          on: 'au.id = us.user_id'
        }
      });
      """
    那么应该生成CTE查询:
      """
      WITH active_users AS (
        SELECT id, name, email
        FROM users
        WHERE status = 'active' AND last_login >= '2024-01-01'
      ),
      user_stats AS (
        SELECT 
          user_id,
          COUNT(*) as order_count,
          SUM(amount) as total_amount
        FROM orders
        WHERE status = 'completed'
        GROUP BY user_id
      )
      SELECT 
        au.id,
        au.name,
        au.email,
        COALESCE(us.order_count, 0) as order_count,
        COALESCE(us.total_amount, 0) as total_amount
      FROM active_users au
      LEFT JOIN user_stats us ON au.id = us.user_id;
      """
    并且应该支持递归CTE
    并且应该支持多个CTE定义
    并且应该优化CTE执行计划

  # 用户故事: US-073 - 支持动态查询构建根据条件灵活构建查询
  场景: 动态查询构建
    假设我需要根据条件动态构建查询
    当我使用查询构建器时:
      """
      const queryBuilder = userRepo.createQueryBuilder('user');
      
      // 动态添加条件
      if (filters.name) {
        queryBuilder.andWhere('user.name LIKE :name', { name: `%${filters.name}%` });
      }
      
      if (filters.ageRange) {
        queryBuilder.andWhere('user.age BETWEEN :minAge AND :maxAge', {
          minAge: filters.ageRange.min,
          maxAge: filters.ageRange.max
        });
      }
      
      if (filters.hasOrders) {
        queryBuilder.innerJoin('user.orders', 'order');
      }
      
      if (filters.sortBy) {
        queryBuilder.orderBy(`user.${filters.sortBy}`, filters.sortOrder || 'ASC');
      }
      
      const users = await queryBuilder.getMany();
      """
    那么应该根据条件生成相应的SQL
    并且应该支持条件的动态组合
    并且应该防止SQL注入
    并且应该优化查询参数
    并且应该支持查询缓存

  # 用户故事: US-074 - 支持复杂聚合查询进行数据统计分析
  场景: 聚合查询和分组
    假设我需要进行复杂的聚合分析
    当我执行聚合查询时:
      """
      const stats = await orderRepo.aggregate({
        groupBy: ['DATE(created_at)', 'status'],
        select: [
          'DATE(created_at) as order_date',
          'status',
          { $count: '*', alias: 'order_count' },
          { $sum: 'amount', alias: 'total_amount' },
          { $avg: 'amount', alias: 'avg_amount' },
          { $min: 'amount', alias: 'min_amount' },
          { $max: 'amount', alias: 'max_amount' },
          { $stddev: 'amount', alias: 'amount_stddev' }
        ],
        having: {
          $count: { $gt: 10 },
          $sum: { $gte: 1000 }
        },
        orderBy: {
          'order_date': 'DESC',
          'total_amount': 'DESC'
        }
      });
      """
    那么应该生成聚合SQL:
      """
      SELECT 
        DATE(created_at) as order_date,
        status,
        COUNT(*) as order_count,
        SUM(amount) as total_amount,
        AVG(amount) as avg_amount,
        MIN(amount) as min_amount,
        MAX(amount) as max_amount,
        STDDEV(amount) as amount_stddev
      FROM orders
      GROUP BY DATE(created_at), status
      HAVING COUNT(*) > 10 AND SUM(amount) >= 1000
      ORDER BY order_date DESC, total_amount DESC;
      """
    并且应该支持多级分组
    并且应该支持复杂的HAVING条件
    并且应该支持自定义聚合函数

  # 用户故事: US-075 - 支持全文搜索实现智能搜索功能
  场景: 全文搜索支持
    假设我需要实现全文搜索功能
    当我执行全文搜索时:
      """
      const results = await productRepo.fullTextSearch({
        query: '高品质 无线 耳机',
        fields: ['name', 'description', 'tags'],
        options: {
          mode: 'natural',        // natural, boolean, query_expansion
          minScore: 0.5,          // 最小相关度分数
          highlight: true,        // 高亮匹配文本
          fuzzy: true,           // 模糊匹配
          synonyms: true         // 同义词扩展
        },
        filters: {
          category: 'electronics',
          price: { $lte: 1000 },
          status: 'active'
        },
        orderBy: {
          $score: 'DESC',        // 按相关度排序
          created_at: 'DESC'
        },
        limit: 20
      });
      """
    那么应该生成全文搜索查询:
      """
      SELECT *,
        MATCH(name, description, tags) AGAINST('高品质 无线 耳机' IN NATURAL LANGUAGE MODE) as score
      FROM products
      WHERE MATCH(name, description, tags) AGAINST('高品质 无线 耳机' IN NATURAL LANGUAGE MODE)
        AND category = 'electronics'
        AND price <= 1000
        AND status = 'active'
        AND MATCH(name, description, tags) AGAINST('高品质 无线 耳机' IN NATURAL LANGUAGE MODE) > 0.5
      ORDER BY score DESC, created_at DESC
      LIMIT 20;
      """
    并且应该支持多种搜索模式
    并且应该支持搜索结果高亮
    并且应该支持搜索建议和自动完成
    并且应该支持搜索统计和分析

  # 用户故事: US-076 - 支持地理空间查询实现基于位置的功能
  场景: 地理空间查询
    假设我需要进行地理位置相关查询
    当我执行地理空间查询时:
      """
      const nearbyStores = await storeRepo.findNearby({
        center: {
          latitude: 39.9042,
          longitude: 116.4074
        },
        radius: 5000,           // 5公里半径
        unit: 'meters',
        select: [
          'id',
          'name',
          'address',
          'latitude',
          'longitude',
          {
            $distance: {
              from: { lat: 39.9042, lng: 116.4074 },
              alias: 'distance'
            }
          }
        ],
        where: {
          status: 'active',
          type: 'retail'
        },
        orderBy: {
          distance: 'ASC'
        },
        limit: 10
      });
      """
    那么应该生成地理空间查询:
      """
      SELECT 
        id,
        name,
        address,
        latitude,
        longitude,
        ST_Distance_Sphere(
          POINT(longitude, latitude),
          POINT(116.4074, 39.9042)
        ) as distance
      FROM stores
      WHERE status = 'active'
        AND type = 'retail'
        AND ST_Distance_Sphere(
          POINT(longitude, latitude),
          POINT(116.4074, 39.9042)
        ) <= 5000
      ORDER BY distance ASC
      LIMIT 10;
      """
    并且应该支持多种地理空间函数
    并且应该支持地理围栏查询
    并且应该支持地理空间索引优化

  # 用户故事: US-077 - 支持时间序列查询分析时间相关的数据
  场景: 时间序列查询
    假设我需要分析时间序列数据
    当我执行时间序列查询时:
      """
      const timeSeries = await metricsRepo.timeSeries({
        timeField: 'timestamp',
        interval: '1h',         // 1小时间隔
        timeRange: {
          start: '2024-01-01T00:00:00Z',
          end: '2024-01-31T23:59:59Z'
        },
        metrics: [
          { field: 'cpu_usage', aggregation: 'avg', alias: 'avg_cpu' },
          { field: 'memory_usage', aggregation: 'max', alias: 'max_memory' },
          { field: 'request_count', aggregation: 'sum', alias: 'total_requests' }
        ],
        groupBy: ['server_id'],
        fillGaps: {
          method: 'linear',     // linear, previous, zero, null
          maxGap: '2h'         // 最大填充间隔
        },
        window: {
          type: 'moving_average',
          size: 3             // 3点移动平均
        }
      });
      """
    那么应该生成时间序列查询
    并且应该支持时间间隔聚合
    并且应该支持数据填充和插值
    并且应该支持滑动窗口计算
    并且应该支持时间序列分析函数

  场景: 查询性能优化
    假设我需要优化查询性能
    当系统检测到慢查询时
    那么应该提供查询优化建议:
      """
      {
        "query": "SELECT u.*, p.* FROM users u LEFT JOIN profiles p ON u.id = p.user_id WHERE u.created_at > ? ORDER BY u.created_at DESC LIMIT 20",
        "executionTime": 1250,
        "analysis": {
          "issues": [
            {
              "type": "MISSING_INDEX",
              "severity": "HIGH",
              "description": "缺少created_at字段的索引",
              "suggestion": "CREATE INDEX idx_users_created_at ON users(created_at DESC)",
              "estimatedImprovement": "80% faster"
            },
            {
              "type": "INEFFICIENT_JOIN",
              "severity": "MEDIUM",
              "description": "LEFT JOIN可能返回大量NULL值",
              "suggestion": "考虑使用INNER JOIN或添加WHERE条件",
              "estimatedImprovement": "30% fewer rows"
            }
          ],
          "recommendations": [
            "添加复合索引: (created_at DESC, status)",
            "考虑分页查询优化",
            "使用查询缓存"
          ]
        }
      }
      """
    并且应该自动应用安全的优化
    并且应该监控优化效果
    并且应该提供查询执行计划分析

  场景: 批量查询操作
    假设我需要执行批量查询操作
    当我执行批量查询时:
      """
      const batchResults = await userRepo.batchQuery([
        {
          operation: 'find',
          params: { where: { status: 'active' }, limit: 100 }
        },
        {
          operation: 'count',
          params: { where: { created_at: { $gte: '2024-01-01' } } }
        },
        {
          operation: 'aggregate',
          params: {
            groupBy: 'status',
            select: [{ $count: '*', alias: 'count' }]
          }
        }
      ]);
      """
    那么应该优化批量查询执行
    并且应该支持查询并行执行
    并且应该支持事务性批量操作
    并且应该提供批量操作进度跟踪
    并且应该支持批量操作的错误处理

  场景: 查询结果缓存
    假设我需要缓存查询结果
    当我执行可缓存的查询时:
      """
      const users = await userRepo.find({
        where: { status: 'active' },
        cache: {
          key: 'active_users',
          ttl: 3600,           // 1小时缓存
          tags: ['users', 'active'],
          invalidateOn: ['user_created', 'user_updated', 'user_deleted']
        }
      });
      """
    那么应该智能缓存查询结果
    并且应该支持缓存键的自动生成
    并且应该支持基于事件的缓存失效
    并且应该支持分层缓存策略
    并且应该提供缓存命中率统计